
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/28/2011 01:29:42
-- Generated from EDMX file: C:\Users\Darren\Documents\Visual Studio 2010\Projects\TimeTracker\TimeTracker_DL\TimeTracker.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [TimeTracker];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Clients'
CREATE TABLE [dbo].[Clients] (
    [ClientId] int IDENTITY(1,1) NOT NULL,
    [ClientName] nvarchar(max)  NOT NULL,
    [HourlyBillingRate] nvarchar(max)  NOT NULL,
    [BillingTermsId] nvarchar(max)  NOT NULL,
    [BillingTerm_BillingTermsId] int  NOT NULL
);
GO

-- Creating table 'BillingTerms'
CREATE TABLE [dbo].[BillingTerms] (
    [BillingTermsId] int IDENTITY(1,1) NOT NULL,
    [BillingTermsDescription] nvarchar(25)  NOT NULL,
    [NumberOfDaysToPay] int  NOT NULL
);
GO

-- Creating table 'BillingCategories'
CREATE TABLE [dbo].[BillingCategories] (
    [BillingCategoryId] int IDENTITY(1,1) NOT NULL,
    [CategoryDescription] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'TimeEntries'
CREATE TABLE [dbo].[TimeEntries] (
    [TimeEntryId] int IDENTITY(1,1) NOT NULL,
    [ClientId] int  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [FromTime] time  NOT NULL,
    [ToTime] time  NOT NULL,
    [TotalTime] decimal(4,2)  NOT NULL,
    [DateWorked] datetime  NOT NULL,
    [IsBillable] bit  NOT NULL,
    [BillingCategoryBillingCategoryId] int  NOT NULL,
    [CreatedAt] datetime  NOT NULL,
    [CreatedBy] nvarchar(25)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ClientId] in table 'Clients'
ALTER TABLE [dbo].[Clients]
ADD CONSTRAINT [PK_Clients]
    PRIMARY KEY CLUSTERED ([ClientId] ASC);
GO

-- Creating primary key on [BillingTermsId] in table 'BillingTerms'
ALTER TABLE [dbo].[BillingTerms]
ADD CONSTRAINT [PK_BillingTerms]
    PRIMARY KEY CLUSTERED ([BillingTermsId] ASC);
GO

-- Creating primary key on [BillingCategoryId] in table 'BillingCategories'
ALTER TABLE [dbo].[BillingCategories]
ADD CONSTRAINT [PK_BillingCategories]
    PRIMARY KEY CLUSTERED ([BillingCategoryId] ASC);
GO

-- Creating primary key on [TimeEntryId] in table 'TimeEntries'
ALTER TABLE [dbo].[TimeEntries]
ADD CONSTRAINT [PK_TimeEntries]
    PRIMARY KEY CLUSTERED ([TimeEntryId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [BillingTerm_BillingTermsId] in table 'Clients'
ALTER TABLE [dbo].[Clients]
ADD CONSTRAINT [FK_ClientBillingTerm]
    FOREIGN KEY ([BillingTerm_BillingTermsId])
    REFERENCES [dbo].[BillingTerms]
        ([BillingTermsId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClientBillingTerm'
CREATE INDEX [IX_FK_ClientBillingTerm]
ON [dbo].[Clients]
    ([BillingTerm_BillingTermsId]);
GO

-- Creating foreign key on [ClientId] in table 'TimeEntries'
ALTER TABLE [dbo].[TimeEntries]
ADD CONSTRAINT [FK_ClientTimeEntry]
    FOREIGN KEY ([ClientId])
    REFERENCES [dbo].[Clients]
        ([ClientId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClientTimeEntry'
CREATE INDEX [IX_FK_ClientTimeEntry]
ON [dbo].[TimeEntries]
    ([ClientId]);
GO

-- Creating foreign key on [BillingCategoryBillingCategoryId] in table 'TimeEntries'
ALTER TABLE [dbo].[TimeEntries]
ADD CONSTRAINT [FK_TimeEntryBillingCategory]
    FOREIGN KEY ([BillingCategoryBillingCategoryId])
    REFERENCES [dbo].[BillingCategories]
        ([BillingCategoryId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TimeEntryBillingCategory'
CREATE INDEX [IX_FK_TimeEntryBillingCategory]
ON [dbo].[TimeEntries]
    ([BillingCategoryBillingCategoryId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------